const fs = require('fs');
const koa = require('koa');
const mysql = require('mysql2/promise');
const Router = require('koa-router');
const bodyParser = require('koa-bodyparser');
const config = require('./config');
const app = new koa();
const router = new Router();
function connFactory() {
  let conn = null;

  return async function () {
    if (!conn) {
      conn = await mysql.createConnection(config.database)
    }
    return conn
  }
}
app.use(bodyParser());
const getConn = connFactory();
router
  .get('/userQuery',async (ctx)=>{
    const query = ctx.request.query;
    let  pageSize = 10;
    let page = parseInt(query.page);
    if (isNaN(page)) {
      page = 1
    }
    let limit = (page - 1) * pageSize;
    let params = [limit, pageSize];
    let params2 = [];
    let sql = 'SELECT account.userId, account.phoneNo, account.createTime, account.accountName,account.idCardNo, '
      + 'account.nickName, account.computingPower,account.balance,reward.rewardCount,rewardsub.reward,'
      + 'account.end_log_time,account.balance_freeze,account.signin_time,account.invitation_count '
      + 'FROM account LEFT JOIN (SELECT uid, SUM(reward) rewardCount FROM rewardrecord GROUP BY uId) reward '
      + 'ON account.userId = reward.uid LEFT JOIN (SELECT uid, SUM(reward) reward FROM rewardrecord '
      + 'WHERE `status` = 1 GROUP BY uId) rewardsub ON account.userId = rewardsub.uid where 1=1 ';
    let sql2 = 'select count(userId) count from account '
                + 'account LEFT JOIN (SELECT uid, SUM(reward) rewardCount FROM rewardrecord GROUP BY uId) reward '
                + 'ON account.userId = reward.uid LEFT JOIN (SELECT uid, SUM(reward) reward FROM rewardrecord '
                + 'WHERE `status` = 1 GROUP BY uId) rewardsub ON account.userId = rewardsub.uid where 1=1 ';
    if (query.phoneNo) {
      sql += ' and account.phoneNo like ?';
      sql2+= ' and account.phoneNo like ?';
      params.splice(0, 0, '%' + query.phoneNo + '%');
      params2.push( '%' + query.phoneNo + '%')
    }
    const conn = await getConn();
    let [rows, fiels] = await conn.query(sql + ' LIMIT ?, ?', params);
    let [counts] = await conn.execute(sql2 ,params2);
    ctx.body ={
      rows,
      count: counts[0] ? counts[0].count : 0
    }
  })



  .get('/userParticulars',async (ctx)=> {
    const query = ctx.request.query;
    let code = 200, msg = '';
    let page = parseInt(query.page);
    if (isNaN(page)) {
      page = 1
    }
    let pageSize = 10;
    let limit = (page - 1) * pageSize;
    let params = [];
    let querySql = ' where 1 = 1 '
    if (query.uId) {
      querySql += ' and uId = ?';
      params.push(query.uid)
    }
    if (query.startDate){
      querySql += ' and receiveTime >= ?';
      params.push(query.startDate);
    }
    if (query.endDate){
      querySql += ' and receiveTime <= ?';
      params.push(query.endDate);
    }
    const conn = await getConn();
    let [rows, fiels] = await conn.query('SELECT rewardId, `status`, receiveReason, reward, receiveTime, '
    + 'rewardId  FROM rewardrecord ' + querySql + ' order by rewardId DESC LIMIT ?, ?', params.concat([limit, pageSize]));
    let [counts] = await conn.execute('select count(rewardid) count from rewardrecord' + querySql, params)
    ctx.body = {
      rows,
      count: counts[0] ? counts[0].count : 0
    }
  })



  .post('/login',async (ctx)=> {
    let code = 200, msg = '';
    const query = ctx.request.body;
    let sql = 'SELECT u.userid, u.username, u.status, r.roleid, r.rolename FROM t_admin u, t_role r where u.username = ? and u.password = ? ';
    if (!query.data.username) {
      code = 2001;
      msg = '请输入用户名';
      ctx.body = {code, msg};
      return
    }
    if (!query.data.password) {
      code = 2001;
      msg = '请输入密码';
      ctx.body = {code, msg};
      return
    }

    const conn = await getConn();
    let [users = []] = await conn.execute(sql, [query.data.username, query.data.password]);
    if (users[0]) {
      if (users[0].status === 0) {
        code = 2005;
        msg = '用户已被冻结,请联系管理员';
        ctx.body = {code, msg};
        return
      }
      let matched = ctx.request.ip.match(/\d+.\d+.\d+.\d+/);
      code = 200;
      msg = '登录成功';
      await conn.execute('update t_admin set last_login_time = ?, login_ip = ?  where userid = ? ', [
        new Date(),
        matched ? matched[0] : '',
        users[0].userid
      ]);
      ctx.body = {code, msg, data: users[0]}
    } else {
      code = 2002;
      msg = '用户名或密码错误';
      ctx.body = {code, msg}
    }
  })

.get('/getFileList',async (ctx)=>{
  let pageSize = 10;
  const query = ctx.request.query;
  let page = parseInt(query.page);
  if (isNaN(page) || page < 1) {
    page = 1
  }
  let limit = (page - 1) * pageSize;
  let params = [limit, pageSize];
  let params2 = [];
  let sql = 'SELECT ' +
    'file.fileId, ' +
    'file.userId, ' +
    'account.nickname, ' +
    // 'file.gameId, ' +
    'file.filepath, ' +
    'file.filepath, ' +
    'file.remark, ' +
    't_evaluate.id evaId, '+
    't_evaluate.evaluate '+
    'FROM ' +
    'file left join account on file.userid = account.userid '+
    'left join t_evaluate on file.fileId = t_evaluate.fileId '
    + ' where 1=1 ';
  let sql2 = 'select count(account.userId) count from file left join account on file.userid = account.userid ' +
    'left join t_evaluate on file.fileId = t_evaluate.fileId  where 1=1 ';
  if (query.nickName) {
    sql += ' and account.nickname like ? ';
    sql2 += ' and account.nickname like ? ';
    params.splice(0, 0, '%' + query.nickName + '%');
    params2.push( '%' + query.nickName + '%')
  }
  const conn = await getConn();
  let [rows, fiels] = await conn.query(sql + 'order by file.fileId DESC LIMIT ?, ?', params);
  let [counts] = await conn.execute(sql2 ,params2);
  ctx.body ={
    code: 200,
    msg: 'success',
    data: {
      rows,
      count: counts[0] ? counts[0].count : 0
    }
  }
})

  .get('/getTags',async (ctx)=>{
    let sql = 'SELECT id, tag_name tagName, `status`, creat_time createTime, update_time updateTime FROM t_tags';
    const conn = await getConn();
    let [rows] = await conn.query(sql);
    ctx.body ={
      code: 200,
      msg: 'success',
      data: rows
    }
  })

  .post('/saveEvalation',async (ctx)=>{
    let {tags = [], evaId, fileId, evalation} = ctx.request.body;
    const conn = await getConn();
    // 筛选出新的tags并保存
    let newTags = [];
    tags.filter(tag => {
      return !/^\d+$/.test(tag)
    }).forEach(async (tag, i) => {
      let [header = {}] = await conn.execute('insert into t_tags(tag_name) values(?)', [tag]);
      newTags.push(header.insertId)
    });
    // console.log('newTags => '+ JSON.stringify(newTags));

    // 保存评级记录
    if (evaId) { // 更新评级
      let [res = {}] = await conn.execute('update t_evaluate set evaluate = ?, update_time = ? where id = ?', [
        evalation,
        new Date(),
        evaId
      ])
      // console.log('res => ' + res)
    } else { // 插入评级
      let [header = {}] = await conn.execute('insert into t_evaluate(fileId, evaluate) values(?, ?)', [fileId, evalation])
      evaId = header.insertId;
      // console.log('insert => ' + evaId)
    }

    if (!evaId) {
      ctx.body = {
        code: 2001,
        msg: '保存失败'
      };
      return
    }

    // 查出数据库中当前评级关联的所有tags
    let [dTags] = await conn.query('select eva_id, tag_id tagId, status, create_time, update_time from t_eva_tag where eva_id = ?', [evaId])
    // 合并请求中新创建的tags和已有的tags
    let tagIds = tags.filter(v => /^\d+$/.test(v)).concat(newTags);
    tagIds.forEach(async id => {
      // 检索数据库中查出的tags中是否已有当前id
      let index = dTags.findIndex(tag => tag.tagId === id);
      if (index > -1) {
        // 数据库中已存在,直接更新
        await conn.execute('update t_eva_tag set status = ?, update_time = ? where eva_id = ? and tag_id = ?', [1, new Date(), evaId, id])
      } else {
        // 数据库中不存在,插入
        await conn.execute('insert into t_eva_tag(eva_id, tag_id) values(?, ?)', [evaId, id])
      }
    });

    // 更新被解除关联关系的记录
    dTags.filter(tag => tagIds.indexOf(tag.tagId) === -1).forEach(async tag => {
      console.log('delete => '+ tag);
      await conn.execute('update t_eva_tag set status = ?, update_time = ? where eva_id = ? and tag_id = ?', [0, new Date(), evaId, tag.tagId])
    });
    ctx.body = {
      code: 200,
      msg: 'success'
    }
  })
  .get('/getEvaTags',async (ctx)=>{
    const query = ctx.request.query;
    if(!query.evaId) {
      ctx.body = {
        code: 2005,
        msg: '参数错误'
      };
      return
    }
    let sql =' SELECT t.id, t.tag_name tagName, t.`status`, t.creat_time, t.update_time FROM ' +
      't_eva_tag e LEFT JOIN t_tags t ON e.tag_id = t.id WHERE e.`status` = 1 and e.eva_id = ? ';
    const conn = await getConn();
    let [tags= []] = await conn.query(sql, [query.evaId]);

    ctx.body={
      code: 200,
      msg: 'success',
      data: tags
    }
});

app.use(router.routes()).use(router.allowedMethods());

app.listen(config.port);
